/*
 * @(#)Sql.java
 * 2012-5-9 下午02:14:01
 * 
 * Copyright (c) 2018-2028, HangZhou QiYun InfoTech Co.,Ltd. .
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.qyxx.jwp.bean;

import java.util.ArrayList;
import java.util.List;

import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElementWrapper;

import org.apache.commons.lang.StringUtils;

/**
 *  sql语句
 *  
 *  @author gxj
 *  @version 1.0 2012-5-9 下午02:14:01
 *  @since jdk1.6
 */
@XmlAccessorType(XmlAccessType.FIELD)
public class Sql {
	
	public static final String CREATE_USERS = "createUsers";
	
	//所有状态
	public static final String STATUS_0 = "status0";
	
	//草稿状态
	public static final String STATUS_1 = "status1";
	
	//审批通过或审核通过状态
	public static final String STATUS_3 = "status3";
	
	//草稿+审批/审核通过状态
	public static final String STATUS_13 = "status13";
	
	//草稿状态
	public static final String STATUS_1_VAL = "'10'";
	
	//审批通过或审核通过状态
	public static final String STATUS_3_VAL = "'31','35'";
	
	//草稿+审批/审核通过状态
	public static final String STATUS_13_VAL = "'10','31','35'";
	
	@XmlAttribute
	private String key;
	@XmlAttribute
	private String caption;

	@XmlElement
	private String content;
	@XmlElement
	private String sqlSelect;
	@XmlElementWrapper(name = "sqlWhere")
	@XmlElement(name="item")
	private List<Item> sqlWhereItemList = new ArrayList<Item>();
	@XmlElementWrapper(name = "setValue")
	@XmlElement(name="item")
	private List<Item> setValueItemList = new ArrayList<Item>();
	
	@XmlElementWrapper(name = "groupBy")
	@XmlElement(name="item")
	private List<Item> groupByItemList = new ArrayList<Item>();
	
	@XmlElementWrapper(name = "orderBy")
	@XmlElement(name="item")
	private List<Item> orderByItemList = new ArrayList<Item>();
	
	@XmlElement
	private Table table;
	
	@XmlElement
	private SelectInfo selectInfo;
	/**
	 * 是否检查数据权限
	 */
	@XmlAttribute
	private Boolean isCheckAuth = false;
	
	/**
	 * sql所属实体名
	 */
	@XmlAttribute
	private String entityName = "";
	
	/**
	 * 数据状态，默认不限状态
	 */
	@XmlAttribute
	private String dataStatus = STATUS_0;
	
	/**
	 * 是否使用缓存
	 */
	@XmlAttribute
	private Boolean isUseCache = false;
	
	
	/**
	 * @return key
	 */
	public String getKey() {
		return key;
	}

	
	/**
	 * @param key
	 */
	public void setKey(String key) {
		this.key = key;
	}

	
	/**
	 * @return caption
	 */
	public String getCaption() {
		return caption;
	}

	
	/**
	 * @param caption
	 */
	public void setCaption(String caption) {
		this.caption = caption;
	}

	/**
	 * @return content
	 */
	public String getContent() {
		return content;
	}
	
	/**
	 * @param content
	 */
	public void setContent(String content) {
		this.content = content;
	}
	
	/**
	 * @return sqlSelect
	 */
	public String getSqlSelect() {
		return sqlSelect;
	}
	
	/**
	 * @param sqlSelect
	 */
	public void setSqlSelect(String sqlSelect) {
		this.sqlSelect = sqlSelect;
	}

	/**
	 * @return sqlWhereItemList
	 */
	public List<Item> getSqlWhereItemList() {
		return sqlWhereItemList;
	}
	
	/**
	 * @param sqlWhereItemList
	 */
	public void setSqlWhereItemList(List<Item> sqlWhereItemList) {
		this.sqlWhereItemList = sqlWhereItemList;
	}
	
	/**
	 * @return setValueItemList
	 */
	public List<Item> getSetValueItemList() {
		return setValueItemList;
	}
	
	/**
	 * @param setValueItemList
	 */
	public void setSetValueItemList(List<Item> setValueItemList) {
		this.setValueItemList = setValueItemList;
	}

	/**
	 * @return the groupByItemList
	 */
	public List<Item> getGroupByItemList() {
		return groupByItemList;
	}

	/**
	 * @param groupByItemList the groupByItemList to set
	 */
	public void setGroupByItemList(List<Item> groupByItemList) {
		this.groupByItemList = groupByItemList;
	}

	/**
	 * @return the orderByItemList
	 */
	public List<Item> getOrderByItemList() {
		return orderByItemList;
	}

	/**
	 * @param orderByItemList the orderByItemList to set
	 */
	public void setOrderByItemList(List<Item> orderByItemList) {
		this.orderByItemList = orderByItemList;
	}

	
	/**
	 * @return table
	 */
	public Table getTable() {
		return table;
	}

	
	/**
	 * @param table
	 */
	public void setTable(Table table) {
		this.table = table;
	}
	

	
	public SelectInfo getSelectInfo() {
		return selectInfo;
	}


	
	public void setSelectInfo(SelectInfo selectInfo) {
		this.selectInfo = selectInfo;
	}


	/**
	 * @return isCheckAuth
	 */
	public Boolean getIsCheckAuth() {
		return isCheckAuth;
	}

	
	/**
	 * @param isCheckAuth
	 */
	public void setIsCheckAuth(Boolean isCheckAuth) {
		this.isCheckAuth = isCheckAuth;
	}

	/**
	 * @return entityName
	 */
	public String getEntityName() {
		return entityName;
	}

	/**
	 * @param entityName
	 */
	public void setEntityName(String entityName) {
		this.entityName = entityName;
	}


	/**
	 * @return dataStatus
	 */
	public String getDataStatus() {
		return dataStatus;
	}


	
	/**
	 * @param dataStatus
	 */
	public void setDataStatus(String dataStatus) {
		this.dataStatus = dataStatus;
	}

	
	/**
	 * @return isUseCache
	 */
	public Boolean getIsUseCache() {
		return isUseCache;
	}


	
	/**
	 * @param isUseCache
	 */
	public void setIsUseCache(Boolean isUseCache) {
		this.isUseCache = isUseCache;
	}


	/**
	 * 动态生成sql语句
	 * 
	 * @param sql
	 * @param isAppendWhere 是否添加where条件
	 * @return
	 */
	public String buildSql(Boolean isAppendWhere) {
		StringBuilder sb = new StringBuilder("");
		if(StringUtils.isBlank(getContent())) {
			sb.append(this.getSqlSelect());
			sb.append(" WHERE 1=1 ");
			if(this.getIsCheckAuth()) {
				//检查数据权限
				sb.append(" AND CREATE_USER IN (:");
				sb.append(CREATE_USERS);
				sb.append(")");
			}
			if (isAppendWhere) {
				// 添加where条件
				List<Item> sqlWhereItems = this.getSqlWhereItemList();
				if (null != sqlWhereItems && !sqlWhereItems.isEmpty()) {
					for (Item item : sqlWhereItems) {
						sb.append(" AND ");
						sb.append(item.getColumn());
						String oper = item.getOperator();
						if(StringUtils.isNotBlank(oper)) {
							sb.append(" ");
							sb.append(oper);
							sb.append(" ");
						} else {
							sb.append(" = ");
						}
						String val = item.getValue();
						if(StringUtils.isNotBlank(val)) {
							sb.append(val);
						} else {
							sb.append(":");
							sb.append(item.getKey());
						}
					}
				}
				
				sb.append(getDataStatus(true));
			}
			// 添加groupBy条件
			List<Item> groupByItems = this.getGroupByItemList();
			if (null != groupByItems && !groupByItems.isEmpty()) {
				sb.append(" GROUP BY ");
				int i = 0;
				for (Item item : groupByItems) {
					if (i > 0) {
						sb.append(" , ");
					}
					sb.append(item.getColumn());
					i++;
				}
			}
			// 添加orderBy条件
			List<Item> orderByItems = this.getOrderByItemList();
			if (null != orderByItems && !orderByItems.isEmpty()) {
				sb.append(" ORDER BY ");
				int i = 0;
				for (Item item : orderByItems) {
					if (i > 0) {
						sb.append(" , ");
					}
					sb.append(item.getColumn());
					sb.append(" ");
					sb.append(StringUtils.defaultIfEmpty(item.getOrder(),
							"ASC"));
					i++;
				}
			}
		} else {
			sb.append(getContent());
		}
		return sb.toString();
	}
	
	/**
	 * 返回Sql语句，包含where条件
	 * 
	 * @return
	 */
	public String buildSql() {
		return buildSql(true);
	}
	
	/**
	 * 返回Hql语句，针对于模块数据源
	 * 
	 * @return
	 */
	public String buildHql() {
		return buildHql(true);
	}
	
	/**
	 * 返回Hql语句，针对于模块数据源
	 * 
	 * @return
	 */
	public String buildHql(Boolean isAppendWhere) {
		StringBuilder sb = new StringBuilder("");
		sb.append("SELECT ");
		List<Item> svtList = this.getSetValueItemList();
		if(null!=svtList && !svtList.isEmpty()) {
			for(int i=0,len=svtList.size();i<len;i++) {
				Item item = svtList.get(i);
				sb.append(item.getColumn());
				sb.append(" AS ");
				sb.append(item.getColumn());
				if(i < len-1) {
					sb.append(",");
				}
			}
		}
		sb.append(" FROM ");
		sb.append(this.getEntityName());
		sb.append(" WHERE 1=1 ");
		if (this.getIsCheckAuth()) {
			// 检查数据权限
			sb.append(" AND createUser IN (:");
			sb.append(CREATE_USERS);
			sb.append(")");
		}
		if(isAppendWhere) {
			sb.append(getDataStatus(false));
		}
		return sb.toString();
	}
	
	/**
	 * 获取sql数据状态
	 * 
	 * @param isColumn 是否字段名
	 * @return
	 */
	private String getDataStatus(boolean isColumn) {
		StringBuilder sb = new StringBuilder("");
		String status = this.getDataStatus();
		if(StringUtils.isNotBlank(status) 
				&& !STATUS_0.equalsIgnoreCase(status)) {
			sb.append(" AND ");
			if(isColumn) {
				sb.append("STATUS");
			} else {
				sb.append("status");
			}
			sb.append(" IN(");
			if(STATUS_1.equals(status)) {
				sb.append(STATUS_1_VAL);
			} else if(STATUS_3.equals(status)){
				sb.append(STATUS_3_VAL);
			} else if(STATUS_13.equals(status)){
				sb.append(STATUS_13_VAL);
			} else {
				sb.append(STATUS_1_VAL);
			}
			sb.append(")");
		}
		return sb.toString();
	}
	
}
